/*
THIS FILE IMPORTS THE INDIVIDUAL INTEREST RATES IN USD AND MERGE THEM IN A SINGLE DATA SET IN .DTA FORMAT
*/

*****************************************************************************
* import individual interest rates and save them manually
* the interest rates are names R1 and R3 and correspond to 1-month and 3-month interest rates expressed in annual percentage points. 
foreach sheet in "australia" "belgium"  "canada" "brazil" "chile" "china" "colombia" "denmark" "france" "finland" "germany" "hongkong" "india" "indonesia" "ireland" "israel" "italy" "japan" "netherlands" "norway" "singapore" "spain" "sweden" "switzerland" "unitedkingdom" "koreasouth" "malaysia" "mexico" "peru" "philippines" "poland" "southafrica" "taiwan" "thailand" "turkey" "unitedstates" {

	display "`sheet'"
	tempfile tempir_`sheet'
	qui import excel "datapath\datastream\interest_rates_clean.xlsx", clear sheet("`sheet'") cellrange(A6) firstrow 
	ds, has(type string) 
	qui foreach v in `r(varlist)' { 
		replace `v' = "" if trim(`v') == "NA" 
	} 
	qui destring _all, replace
	qui gen month  = mofd(date)
	format month %tm
	qui drop date
	qui save "`tempir_`sheet''", replace
}
*****************************************************************************

*****************************************************************************
* merge individual data 
use "`tempir_unitedstates'", clear
foreach sheet in "australia" "belgium"  "canada" "brazil" "chile" "china" "colombia" "denmark" "france" "finland" "germany" "hongkong" "india" "indonesia" "ireland" "israel" "italy" "japan" "netherlands" "norway" "singapore" "spain" "sweden" "switzerland" "unitedkingdom" "koreasouth" "malaysia" "mexico" "peru" "philippines" "poland" "southafrica" "taiwan" "thailand" "turkey" {
	cap merge 1:1 month using "`tempir_`sheet''", nogenerate
}
*****************************************************************************

*****************************************************************************
* express annualized interest rates in percent to monthly interest rate
gen R1m = R1/1200
gen R3m = R3/1200

* first differences
bys country (datem): gen dR3m = R3m - R3m[_n-1]
bys country (datem): gen dR1m = R1m - R1m[_n-1]

save "datapath\Interest_Rates_exceldata_to_DTA.dta", replace
* end
*****************************************************************************